﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Meeting.DBUtility;
using Meeting.Model;
using Newtonsoft.Json;
using Newtonsoft.Json.Converters;
using System.Collections;
namespace Meeting.BLL
{
    public class TopicBLL
    {
        public string errorString = "";

        /// <summary>
        /// 增加一条议题
        /// </summary>
        public string Add(Meeting.Model.Topic model)
        {
            JsonResponse response = new JsonResponse();

            try
            {
                List<string> sqlList = new List<string>();
                Guid topicId = Guid.NewGuid();
                sqlList.Add(string.Format("INSERT INTO [Topic]([Id],[CategoryId],[Title],[Details],[EmployeeId],[EmployeeName],[EmployeeTitle],[RoleId])VALUES('{0}',{1},'{2}','{3}','{4}','{5}','{6}','{7}')"
                    , topicId, model.CategoryId, model.Title, model.Details, model.EmployeeId, model.EmployeeName, model.EmployeeTitle, model.RoleId));
                //分析征求对象人员列表
                List<string> asks = model.Asks.Split(',').ToList();
                asks.ForEach(x =>
                {
                    if (!string.IsNullOrEmpty(x))
                    {
                        //排除自身
                        if (x.Split('|')[1] != model.EmployeeId)
                        {
                            sqlList.Add(string.Format("INSERT INTO [TopicApprove]([TopicId],[RoleId],[EmployeeId],[EmployeeName],[EmployeeTitle],[Mobile])VALUES('{0}','{1}','{2}','{3}','{4}','{5}')",
                                topicId, x.Split('|')[0], x.Split('|')[1], x.Split('|')[2], x.Split('|')[3], x.Split('|')[4]));
                        }
                    }
                });


                int rows = DbHelperSQL.ExecuteSqlTran(sqlList);
                if (rows > 0)
                {
                    response.success = true;
                    response.ResponseData = topicId;
                }
                else
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }
                return JsonConvert.SerializeObject(response);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response);
            }
        }

        /// <summary>
        /// 更新议题
        /// </summary>
        public string Update(Meeting.Model.Topic model)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                List<string> sqlList = new List<string>();
                sqlList.Add(string.Format("UPDATE [Topic] SET [Title] = '{1}',[Details]='{2}',[CategoryId] = {3} where [Id] = '{0}'"
                    , model.Id, model.Title, model.Details, model.CategoryId));
                //如果是更新，首先清除原有的人员列表
                sqlList.Add(string.Format("delete from TopicApprove where TopicId = '{0}'", model.Id));
                //分析征求对象人员列表
                List<string> asks = model.Asks.Split(',').ToList();
                asks.ForEach(x =>
                {
                    if (!string.IsNullOrEmpty(x))
                    {
                        sqlList.Add(string.Format("INSERT INTO [TopicApprove]([TopicId],[RoleId],[EmployeeId],[EmployeeName],[EmployeeTitle],[Mobile])VALUES('{0}','{1}','{2}','{3}','{4}','{5}')",
                            model.Id, x.Split('|')[0], x.Split('|')[1], x.Split('|')[2], x.Split('|')[3] == null ? "" : x.Split('|')[3], x.Split('|')[4]));
                    }
                });

                int rows = DbHelperSQL.ExecuteSqlTran(sqlList);
                if (rows > 0)
                {
                    response.success = true;
                    response.ResponseData = model.Id;
                }
                else
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }
                return JsonConvert.SerializeObject(response);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response);
            }
        }

        /// <summary>
        /// 更新议题说明
        /// </summary>
        /// <param name="topicId"></param>
        /// <param name="desc"></param>
        /// <returns></returns>
        public string UpdateDescription(Guid topicId, string desc, string agent, string extNumber)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                string sql = "UPDATE [Topic] set [Description] = '{1}',[Agent] = '{2}',[ExtNumber] = '{3}' where Id = '{0}'";
                int rows = DbHelperSQL.ExecuteSql(string.Format(sql, topicId, desc, agent, extNumber));
                if (rows > 0 || string.IsNullOrEmpty(DbHelperSQL.errorString))
                {
                    response.success = true;
                }
                else
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }

                return JsonConvert.SerializeObject(response);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response);
            }
        }

        ///// <summary>
        ///// 更新一条数据
        ///// </summary>
        //public bool Update(Meeting.Model.Topic model)
        //{
        //    StringBuilder strSql = new StringBuilder();
        //    strSql.Append("update Topic set ");
        //    strSql.Append("Title=@Title,");
        //    strSql.Append("Description=@Description,");
        //    strSql.Append("EmployeeId=@EmployeeId,");
        //    strSql.Append("EmployeeName=@EmployeeName,");
        //    strSql.Append("RoleId=@RoleId,");
        //    strSql.Append("CreateTime=@CreateTime,");
        //    strSql.Append("Comment=@Comment,");
        //    strSql.Append("IsActive=@IsActive");
        //    strSql.Append(" where Id=@Id ");
        //    SqlParameter[] parameters = {
        //            new SqlParameter("@Title", SqlDbType.NVarChar,500),
        //            new SqlParameter("@Description", SqlDbType.Text),
        //            new SqlParameter("@EmployeeId", SqlDbType.NVarChar,64),
        //            new SqlParameter("@EmployeeName", SqlDbType.NVarChar,64),
        //            new SqlParameter("@RoleId", SqlDbType.NVarChar,1024),
        //            new SqlParameter("@CreateTime", SqlDbType.DateTime),
        //            new SqlParameter("@Comment", SqlDbType.NVarChar,500),
        //            new SqlParameter("@IsActive", SqlDbType.Bit,1),
        //            new SqlParameter("@Id", SqlDbType.UniqueIdentifier,16)};
        //    parameters[0].Value = model.Title;
        //    parameters[1].Value = model.Description;
        //    parameters[2].Value = model.EmployeeId;
        //    parameters[3].Value = model.EmployeeName;
        //    parameters[4].Value = model.RoleId;
        //    parameters[5].Value = model.CreateTime;
        //    parameters[6].Value = model.Comment;
        //    parameters[7].Value = model.IsActive;
        //    parameters[8].Value = model.Id;

        //    int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        //    if (rows > 0)
        //    {
        //        return true;
        //    }
        //    else
        //    {
        //        return false;
        //    }
        //}

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(Guid Id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from Topic ");
            strSql.Append(" where Id=@Id ");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.UniqueIdentifier,16)};
            parameters[0].Value = Id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 获取议题的详细信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string GetTopicBy(Guid topicId)
        {
            JsonResponse response = new JsonResponse();
            //获取议题
            string sql = @"select * from Topic where Id = '{0}'
                           select * from TopicDocument where TopicId='{0}' order by DocumentCategory,Sort,UploadDate                          
                           select * from TopicApprove where TopicId = '{0}'
                           select * from TopicPlate where TopicId='{0}' order by Sort,UploadDate";

            try
            {
                DataSet ds = DbHelperSQL.Query(string.Format(sql, topicId));
                //
                if (ds == null)
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }
                else
                {
                    response.success = true;
                    Model.Topic model = new Model.Topic();

                    #region 转换议题基本信息
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        if (ds.Tables[0].Rows[0]["Id"] != null && ds.Tables[0].Rows[0]["Id"].ToString() != "")
                        {
                            model.Id = new Guid(ds.Tables[0].Rows[0]["Id"].ToString());
                        }
                        if (ds.Tables[0].Rows[0]["CategoryId"] != null && ds.Tables[0].Rows[0]["CategoryId"].ToString() != "")
                        {
                            model.CategoryId = int.Parse(ds.Tables[0].Rows[0]["CategoryId"].ToString());
                        }
                        if (ds.Tables[0].Rows[0]["Title"] != null && ds.Tables[0].Rows[0]["Title"].ToString() != "")
                        {
                            model.Title = ds.Tables[0].Rows[0]["Title"].ToString();
                        }
                        if (ds.Tables[0].Rows[0]["Details"] != null && ds.Tables[0].Rows[0]["Details"].ToString() != "")
                        {
                            model.Details = ds.Tables[0].Rows[0]["Details"].ToString();
                        }
                        if (ds.Tables[0].Rows[0]["Description"] != null && ds.Tables[0].Rows[0]["Description"].ToString() != "")
                        {
                            model.Description = ds.Tables[0].Rows[0]["Description"].ToString();
                        }
                        if (ds.Tables[0].Rows[0]["EmployeeId"] != null && ds.Tables[0].Rows[0]["EmployeeId"].ToString() != "")
                        {
                            model.EmployeeId = ds.Tables[0].Rows[0]["EmployeeId"].ToString();
                        }
                        if (ds.Tables[0].Rows[0]["EmployeeName"] != null && ds.Tables[0].Rows[0]["EmployeeName"].ToString() != "")
                        {
                            model.EmployeeName = ds.Tables[0].Rows[0]["EmployeeName"].ToString();
                        }
                        if (ds.Tables[0].Rows[0]["RoleId"] != null && ds.Tables[0].Rows[0]["RoleId"].ToString() != "")
                        {
                            model.RoleId = ds.Tables[0].Rows[0]["RoleId"].ToString();
                        }
                        if (ds.Tables[0].Rows[0]["CreateTime"] != null && ds.Tables[0].Rows[0]["CreateTime"].ToString() != "")
                        {
                            model.CreateTime = DateTime.Parse(ds.Tables[0].Rows[0]["CreateTime"].ToString());
                        }
                        if (ds.Tables[0].Rows[0]["Comment"] != null && ds.Tables[0].Rows[0]["Comment"].ToString() != "")
                        {
                            model.Comment = ds.Tables[0].Rows[0]["Comment"].ToString();
                        }
                        if (ds.Tables[0].Rows[0]["IsActive"] != null && ds.Tables[0].Rows[0]["IsActive"].ToString() != "")
                        {
                            if ((ds.Tables[0].Rows[0]["IsActive"].ToString() == "1") || (ds.Tables[0].Rows[0]["IsActive"].ToString().ToLower() == "true"))
                            {
                                model.IsActive = true;
                            }
                            else
                            {
                                model.IsActive = false;
                            }
                        }
                        if (ds.Tables[0].Rows[0]["IsSubmit"] != null && ds.Tables[0].Rows[0]["IsSubmit"].ToString() != "")
                        {
                            if ((ds.Tables[0].Rows[0]["IsSubmit"].ToString() == "1") || (ds.Tables[0].Rows[0]["IsSubmit"].ToString().ToLower() == "true"))
                            {
                                model.IsSubmit = true;
                            }
                            else
                            {
                                model.IsSubmit = false;
                            }
                        }
                        if (ds.Tables[0].Rows[0]["DocumentCount"] != null && ds.Tables[0].Rows[0]["DocumentCount"].ToString() != "")
                        {
                            model.DocumentCount = int.Parse(ds.Tables[0].Rows[0]["DocumentCount"].ToString());
                        }
                        if (ds.Tables[0].Rows[0]["Agent"] != null && ds.Tables[0].Rows[0]["Agent"].ToString() != "")
                        {
                            model.Agent = ds.Tables[0].Rows[0]["Agent"].ToString();
                        }
                        if (ds.Tables[0].Rows[0]["ExtNumber"] != null && ds.Tables[0].Rows[0]["ExtNumber"].ToString() != "")
                        {
                            model.ExtNumber = ds.Tables[0].Rows[0]["ExtNumber"].ToString();
                        }
                    }
                    #endregion

                    #region 转换议题文档
                    model.Documents = new List<Model.TopicDocument>();
                    int rowsCount = ds.Tables[1].Rows.Count;
                    if (rowsCount > 0)
                    {
                        Model.TopicDocument doc;
                        for (int n = 0; n < rowsCount; n++)
                        {
                            doc = new Meeting.Model.TopicDocument();
                            if (ds.Tables[1].Rows[n]["Id"] != null && ds.Tables[1].Rows[n]["Id"].ToString() != "")
                            {
                                doc.Id = new Guid(ds.Tables[1].Rows[n]["Id"].ToString());
                            }
                            if (ds.Tables[1].Rows[n]["TopicId"] != null && ds.Tables[1].Rows[n]["TopicId"].ToString() != "")
                            {
                                doc.TopicId = new Guid(ds.Tables[1].Rows[n]["TopicId"].ToString());
                            }
                            if (ds.Tables[1].Rows[n]["Sort"] != null && ds.Tables[1].Rows[n]["Sort"].ToString() != "")
                            {
                                doc.Sort = int.Parse(ds.Tables[1].Rows[n]["Sort"].ToString());
                            }
                            if (ds.Tables[1].Rows[n]["Path"] != null && ds.Tables[1].Rows[n]["Path"].ToString() != "")
                            {
                                doc.Path = ds.Tables[1].Rows[n]["Path"].ToString();
                            }
                            if (ds.Tables[1].Rows[n]["Name"] != null && ds.Tables[1].Rows[n]["Name"].ToString() != "")
                            {
                                doc.Name = ds.Tables[1].Rows[n]["Name"].ToString();
                            }
                            if (ds.Tables[1].Rows[n]["Format"] != null && ds.Tables[1].Rows[n]["Format"].ToString() != "")
                            {
                                doc.Format = ds.Tables[1].Rows[n]["Format"].ToString();
                            }
                            if (ds.Tables[1].Rows[n]["UploadDate"] != null && ds.Tables[1].Rows[n]["UploadDate"].ToString() != "")
                            {
                                doc.UploadDate = DateTime.Parse(ds.Tables[1].Rows[n]["UploadDate"].ToString());
                            }
                            if (ds.Tables[1].Rows[n]["Comment"] != null && ds.Tables[1].Rows[n]["Comment"].ToString() != "")
                            {
                                doc.Comment = ds.Tables[1].Rows[n]["Comment"].ToString();
                            }
                            if (ds.Tables[1].Rows[n]["IsActive"] != null && ds.Tables[1].Rows[n]["IsActive"].ToString() != "")
                            {
                                if ((ds.Tables[1].Rows[n]["IsActive"].ToString() == "1") || (ds.Tables[1].Rows[n]["IsActive"].ToString().ToLower() == "true"))
                                {
                                    doc.IsActive = true;
                                }
                                else
                                {
                                    doc.IsActive = false;
                                }
                            }
                            if (ds.Tables[1].Rows[n]["ImageCount"] != null && ds.Tables[1].Rows[n]["ImageCount"].ToString() != "")
                            {
                                doc.ImageCount = int.Parse(ds.Tables[1].Rows[n]["ImageCount"].ToString());
                            }
                            if (ds.Tables[1].Rows[n]["Type"] != null && ds.Tables[1].Rows[n]["Type"].ToString() != "")
                            {
                                if ((ds.Tables[1].Rows[n]["Type"].ToString() == "1") || (ds.Tables[1].Rows[n]["Type"].ToString().ToLower() == "true"))
                                {
                                    doc.Type = true;
                                }
                                else
                                {
                                    doc.Type = false;
                                }
                            }
                            if (ds.Tables[1].Rows[n]["DocumentCategory"] != null && ds.Tables[1].Rows[n]["DocumentCategory"].ToString() != "")
                            {
                                doc.DocumentCategory = int.Parse(ds.Tables[1].Rows[n]["DocumentCategory"].ToString());
                            }
                            model.Documents.Add(doc);
                        }
                    }
                    #endregion
               

                    #region 转换议题签署意见
                    model.Approves = new List<TopicApprove>();
                    rowsCount = ds.Tables[2].Rows.Count;
                    if (rowsCount > 0)
                    {
                        Model.TopicApprove approve;
                        for (int n = 0; n < rowsCount; n++)
                        {
                            approve = new Model.TopicApprove();
                            if (ds.Tables[2].Rows[n]["TopicId"] != null && ds.Tables[2].Rows[n]["TopicId"].ToString() != "")
                            {
                                approve.TopicId = new Guid(ds.Tables[2].Rows[n]["TopicId"].ToString());
                            }
                            if (ds.Tables[2].Rows[n]["EmployeeId"] != null && ds.Tables[2].Rows[n]["EmployeeId"].ToString() != "")
                            {
                                approve.EmployeeId = ds.Tables[2].Rows[n]["EmployeeId"].ToString();
                            }
                            if (ds.Tables[2].Rows[n]["EmployeeName"] != null && ds.Tables[2].Rows[n]["EmployeeName"].ToString() != "")
                            {
                                approve.EmployeeName = ds.Tables[2].Rows[n]["EmployeeName"].ToString();
                            }
                            if (ds.Tables[2].Rows[n]["EmployeeTitle"] != null && ds.Tables[2].Rows[n]["EmployeeTitle"].ToString() != "")
                            {
                                approve.EmployeeTitle = ds.Tables[2].Rows[n]["EmployeeTitle"].ToString();
                            }
                            if (ds.Tables[2].Rows[n]["RoleId"] != null && ds.Tables[2].Rows[n]["RoleId"].ToString() != "")
                            {
                                approve.RoleId = ds.Tables[2].Rows[n]["RoleId"].ToString();
                            }
                            if (ds.Tables[2].Rows[n]["IsApprove"] != null && ds.Tables[2].Rows[n]["IsApprove"].ToString() != "")
                            {
                                if ((ds.Tables[2].Rows[n]["IsApprove"].ToString() == "1") || (ds.Tables[2].Rows[n]["IsApprove"].ToString().ToLower() == "true"))
                                {
                                    approve.IsApprove = true;
                                }
                                else
                                {
                                    approve.IsApprove = false;
                                }
                            }
                            if (ds.Tables[2].Rows[n]["IsPass"] != null && ds.Tables[2].Rows[n]["IsPass"].ToString() != "")
                            {
                                if ((ds.Tables[2].Rows[n]["IsPass"].ToString() == "1") || (ds.Tables[2].Rows[n]["IsPass"].ToString().ToLower() == "true"))
                                {
                                    approve.IsPass = true;
                                }
                                else
                                {
                                    approve.IsPass = false;
                                }
                            }
                            if (ds.Tables[2].Rows[n]["Comment"] != null && ds.Tables[2].Rows[n]["Comment"].ToString() != "")
                            {
                                approve.Comment = ds.Tables[2].Rows[n]["Comment"].ToString();
                            }
                            if (ds.Tables[2].Rows[n]["ApproveDate"] != null && ds.Tables[2].Rows[n]["ApproveDate"].ToString() != "")
                            {
                                approve.ApproveDate = DateTime.Parse(ds.Tables[2].Rows[n]["ApproveDate"].ToString());
                            }
                            if (ds.Tables[2].Rows[n]["InputType"] != null && ds.Tables[2].Rows[n]["InputType"].ToString() != "")
                            {
                                approve.InputType = Convert.ToInt32(ds.Tables[2].Rows[n]["InputType"].ToString());
                            }
                            if (ds.Tables[2].Rows[n]["Path"] != null && ds.Tables[2].Rows[n]["Path"].ToString() != "")
                            {
                                approve.Path = ds.Tables[2].Rows[n]["Path"].ToString();
                            }
                            model.Approves.Add(approve);
                        }
                    }
                    #endregion

                    #region 转换地块文档
                    model.Plates = new List<Model.TopicPlate>();
                    rowsCount = ds.Tables[3].Rows.Count;
                    if (rowsCount > 0)
                    {
                        Model.TopicPlate pla;
                        for (int n = 0; n < rowsCount; n++)
                        {
                            pla = new Meeting.Model.TopicPlate();
                            if (ds.Tables[3].Rows[n]["Id"] != null && ds.Tables[3].Rows[n]["Id"].ToString() != "")
                            {
                                pla.Id = new Guid(ds.Tables[3].Rows[n]["Id"].ToString());
                            }
                            if (ds.Tables[3].Rows[n]["TopicId"] != null && ds.Tables[3].Rows[n]["TopicId"].ToString() != "")
                            {
                                pla.TopicId = new Guid(ds.Tables[3].Rows[n]["TopicId"].ToString());
                            }
                            if (ds.Tables[3].Rows[n]["Sort"] != null && ds.Tables[3].Rows[n]["Sort"].ToString() != "")
                            {
                                pla.Sort = int.Parse(ds.Tables[3].Rows[n]["Sort"].ToString());
                            }
                            if (ds.Tables[3].Rows[n]["Path"] != null && ds.Tables[3].Rows[n]["Path"].ToString() != "")
                            {
                                pla.Path = ds.Tables[3].Rows[n]["Path"].ToString();
                            }
                            if (ds.Tables[3].Rows[n]["Name"] != null && ds.Tables[3].Rows[n]["Name"].ToString() != "")
                            {
                                pla.Name = ds.Tables[3].Rows[n]["Name"].ToString();
                            }
                            if (ds.Tables[3].Rows[n]["Format"] != null && ds.Tables[3].Rows[n]["Format"].ToString() != "")
                            {
                                pla.Format = ds.Tables[3].Rows[n]["Format"].ToString();
                            }
                            if (ds.Tables[3].Rows[n]["UploadDate"] != null && ds.Tables[3].Rows[n]["UploadDate"].ToString() != "")
                            {
                                pla.UploadDate = DateTime.Parse(ds.Tables[3].Rows[n]["UploadDate"].ToString());
                            }
                            if (ds.Tables[3].Rows[n]["Comment"] != null && ds.Tables[3].Rows[n]["Comment"].ToString() != "")
                            {
                                pla.Comment = ds.Tables[3].Rows[n]["Comment"].ToString();
                            }
                            if (ds.Tables[3].Rows[n]["IsActive"] != null && ds.Tables[3].Rows[n]["IsActive"].ToString() != "")
                            {
                                if ((ds.Tables[3].Rows[n]["IsActive"].ToString() == "1") || (ds.Tables[3].Rows[n]["IsActive"].ToString().ToLower() == "true"))
                                {
                                    pla.IsActive = true;
                                }
                                else
                                {
                                    pla.IsActive = false;
                                }
                            }                          
                
                            model.Plates.Add(pla);
                        }
                    }
                    #endregion

                    response.ResponseData = model;
                }

                IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
                timeConverter.DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss";

                return JsonConvert.SerializeObject(response, timeConverter);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response); ;
            }
        }

        /// <summary>
        /// 获取我的议题
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string GetTopicsBy(string empId)
        {
            JsonResponse response = new JsonResponse();
            //获取议题
            //            string sql = @"select *,
            //                            (SELECT count([TopicId]) FROM TopicApprove where TopicId = Topic.Id and IsApprove = 1) as SignNumber,
            //                            (SELECT count([TopicId]) FROM TopicApprove where TopicId = Topic.Id and IsApprove = 0) as UnSignNumber FROM Topic where EmployeeId = '{0}'";
            string sql = @"select *,
                            (SELECT count([EmployeeId]) FROM TopicApprove where TopicId = Topic.Id and IsApprove = 1) as SignNumber,
                            (SELECT count([EmployeeId]) FROM TopicApprove where TopicId = Topic.Id and IsApprove = 0) as UnSignNumber,(Select Count(*) From MeetingTopic where TopicId=Id) As MeetingNumber FROM Topic where EmployeeId = '{0}' Order by CreateTime desc";
            try
            {
                DataSet ds = DbHelperSQL.Query(string.Format(sql, empId));
                //
                if (ds == null)
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }
                else
                {
                    response.success = true;
                    response.ResponseData = ds.Tables[0];
                }


                IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
                timeConverter.DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss";


                return JsonConvert.SerializeObject(response, timeConverter);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response); ;
            }
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public string AddDocument(Meeting.Model.TopicDocument model)
        {
            return AddDocument(model, false);
        }
        public string AddDocument(Meeting.Model.TopicDocument model, bool bFlagWriteMeetingDocument)
        {
            JsonResponse response = new JsonResponse();

            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("insert into TopicDocument(");
                strSql.Append("Id,TopicId,Sort,Path,Name,Format,UploadDate,Comment,IsActive,ImageCount,Type,MeetingId,DocumentCategory)");
                strSql.Append(" values (");
                strSql.Append("@Id,@TopicId,@Sort,@Path,@Name,@Format,@UploadDate,@Comment,@IsActive,@ImageCount,@Type,@MeetingId,@DocumentCategory)");
                SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@TopicId", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@Sort", SqlDbType.Int,4),
					new SqlParameter("@Path", SqlDbType.NVarChar,128),
					new SqlParameter("@Name", SqlDbType.NVarChar,256),
					new SqlParameter("@Format", SqlDbType.VarChar,50),
					new SqlParameter("@UploadDate", SqlDbType.DateTime),
					new SqlParameter("@Comment", SqlDbType.NVarChar,200),
					new SqlParameter("@IsActive", SqlDbType.Bit,1),
					new SqlParameter("@ImageCount", SqlDbType.Int,4),
					new SqlParameter("@Type", SqlDbType.Bit,1),
					new SqlParameter("@MeetingId", SqlDbType.Int,4),
                    new SqlParameter("@DocumentCategory", SqlDbType.Int,4)};

                parameters[0].Value = model.Id;
                parameters[1].Value = model.TopicId;
                parameters[2].Value = model.Sort;
                parameters[3].Value = model.Path;
                parameters[4].Value = model.Name;
                parameters[5].Value = model.Format;
                parameters[6].Value = model.UploadDate;
                parameters[7].Value = model.Comment;
                parameters[8].Value = model.IsActive;
                parameters[9].Value = model.ImageCount;
                parameters[10].Value = model.Type;
                parameters[11].Value = model.MeetingId;
                parameters[12].Value = model.DocumentCategory;

                List<DictionaryEntry> dicList = new List<DictionaryEntry>();
                dicList.Add(new DictionaryEntry(strSql.ToString(), parameters));
                if (bFlagWriteMeetingDocument) //写入关联表
                {
                    strSql = new StringBuilder();
                    strSql.Append("insert into MeetingTopicDocument(");
                    strSql.Append("MeetingId,TopicId,DocumentId)");
                    strSql.Append(" values (");
                    strSql.Append("@MeetingId,@TopicId,@DocumentId)");
                    SqlParameter[] parameters1 = {
					new SqlParameter("@MeetingId", SqlDbType.Int,4),
					new SqlParameter("@TopicId", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@DocumentId", SqlDbType.UniqueIdentifier,16)};
                    parameters1[0].Value = model.MeetingId;
                    parameters1[1].Value = model.TopicId;
                    parameters1[2].Value = model.Id;
                    dicList.Add(new DictionaryEntry(strSql.ToString(), parameters1));
                }

                try
                {
                    DbHelperSQL.ExecuteSqlTran(dicList);
                    response.success = true;
                    response.ResponseData = model;
                }
                catch (Exception ex)
                {
                    response.success = false;
                    response.Message = ex.Message;
                }

                return JsonConvert.SerializeObject(response);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response);
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool UpdateDocument(Meeting.Model.TopicDocument model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update TopicDocument set ");
            strSql.Append("TopicId=@TopicId,");
            strSql.Append("Sort=@Sort,");
            strSql.Append("Path=@Path,");
            strSql.Append("Name=@Name,");
            strSql.Append("Format=@Format,");
            strSql.Append("UploadDate=@UploadDate,");
            strSql.Append("Comment=@Comment,");
            strSql.Append("IsActive=@IsActive,");
            strSql.Append("ImageCount=@ImageCount,");
            strSql.Append("Type=@Type");
            strSql.Append(" where Id=@Id ");
            SqlParameter[] parameters = {
					new SqlParameter("@TopicId", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@Sort", SqlDbType.Int,4),
					new SqlParameter("@Path", SqlDbType.NVarChar,128),
					new SqlParameter("@Name", SqlDbType.NVarChar,256),
					new SqlParameter("@Format", SqlDbType.VarChar,50),
					new SqlParameter("@UploadDate", SqlDbType.DateTime),
					new SqlParameter("@Comment", SqlDbType.NVarChar,200),
					new SqlParameter("@IsActive", SqlDbType.Bit,1),
					new SqlParameter("@ImageCount", SqlDbType.Int,4),
					new SqlParameter("@Type", SqlDbType.Bit,1),
					new SqlParameter("@Id", SqlDbType.UniqueIdentifier,16)};
            parameters[0].Value = model.TopicId;
            parameters[1].Value = model.Sort;
            parameters[2].Value = model.Path;
            parameters[3].Value = model.Name;
            parameters[4].Value = model.Format;
            parameters[5].Value = model.UploadDate;
            parameters[6].Value = model.Comment;
            parameters[7].Value = model.IsActive;
            parameters[8].Value = model.ImageCount;
            parameters[9].Value = model.Type;
            parameters[10].Value = model.Id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 增加一条地块数据
        /// </summary>
        public string AddPlate(Meeting.Model.TopicPlate model)
        {
            return AddPlate(model, false);
        }
        public string AddPlate(Meeting.Model.TopicPlate model, bool bFlagWriteMeetingPlate)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("insert into TopicPlate(");
                strSql.Append("Id,TopicId,Sort,Path,Name,Format,UploadDate,Comment,IsActive,MeetingId)");
                strSql.Append(" values (");
                strSql.Append("@Id,@TopicId,@Sort,@Path,@Name,@Format,@UploadDate,@Comment,@IsActive,@MeetingId)");
                SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@TopicId", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@Sort", SqlDbType.Int,4),
					new SqlParameter("@Path", SqlDbType.NVarChar,128),
					new SqlParameter("@Name", SqlDbType.NVarChar,256),
					new SqlParameter("@Format", SqlDbType.VarChar,50),
					new SqlParameter("@UploadDate", SqlDbType.DateTime),
					new SqlParameter("@Comment", SqlDbType.NVarChar,200),
					new SqlParameter("@IsActive", SqlDbType.Bit,1),
					new SqlParameter("@MeetingId", SqlDbType.Int,4)};
                parameters[0].Value = model.Id;
                parameters[1].Value = model.TopicId;
                parameters[2].Value = model.Sort;
                parameters[3].Value = model.Path;
                parameters[4].Value = model.Name;
                parameters[5].Value = model.Format;
                parameters[6].Value = model.UploadDate;
                parameters[7].Value = model.Comment;
                parameters[8].Value = model.IsActive;
                parameters[9].Value = model.MeetingId;

                List<DictionaryEntry> dicList = new List<DictionaryEntry>();
                dicList.Add(new DictionaryEntry(strSql.ToString(), parameters));
                if (bFlagWriteMeetingPlate) //写入关联表
                {
                    strSql = new StringBuilder();
                    strSql.Append("insert into MeetingTopicPlate(");
                    strSql.Append("MeetingId,TopicId,PlateId)");
                    strSql.Append(" values (");
                    strSql.Append("@MeetingId,@TopicId,@PlateId)");
                    SqlParameter[] parameters1 = {
					new SqlParameter("@MeetingId", SqlDbType.Int,4),
					new SqlParameter("@TopicId", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@PlateId", SqlDbType.UniqueIdentifier,16)};
                    parameters1[0].Value = model.MeetingId;
                    parameters1[1].Value = model.TopicId;
                    parameters1[2].Value = model.Id;

                    dicList.Add(new DictionaryEntry(strSql.ToString(), parameters1));
                }

                try
                {
                    DbHelperSQL.ExecuteSqlTran(dicList);
                    response.success = true;
                    response.ResponseData = model;
                }
                catch (Exception ex)
                {
                    response.success = false;
                    response.Message = ex.Message;
                }

                return JsonConvert.SerializeObject(response);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response);
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Meeting.Model.TopicDocument GetDocBy(Guid Id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 Id,TopicId,Sort,Path,Name,Format,UploadDate,Comment,IsActive,ImageCount,Type,DocumentCategory from TopicDocument ");
            strSql.Append(" where Id=@Id ");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.UniqueIdentifier,16)};
            parameters[0].Value = Id;

            Meeting.Model.TopicDocument model = new Meeting.Model.TopicDocument();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["Id"] != null && ds.Tables[0].Rows[0]["Id"].ToString() != "")
                {
                    model.Id = new Guid(ds.Tables[0].Rows[0]["Id"].ToString());
                }
                if (ds.Tables[0].Rows[0]["TopicId"] != null && ds.Tables[0].Rows[0]["TopicId"].ToString() != "")
                {
                    model.TopicId = new Guid(ds.Tables[0].Rows[0]["TopicId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Sort"] != null && ds.Tables[0].Rows[0]["Sort"].ToString() != "")
                {
                    model.Sort = int.Parse(ds.Tables[0].Rows[0]["Sort"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Path"] != null && ds.Tables[0].Rows[0]["Path"].ToString() != "")
                {
                    model.Path = ds.Tables[0].Rows[0]["Path"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Name"] != null && ds.Tables[0].Rows[0]["Name"].ToString() != "")
                {
                    model.Name = ds.Tables[0].Rows[0]["Name"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Format"] != null && ds.Tables[0].Rows[0]["Format"].ToString() != "")
                {
                    model.Format = ds.Tables[0].Rows[0]["Format"].ToString();
                }
                if (ds.Tables[0].Rows[0]["UploadDate"] != null && ds.Tables[0].Rows[0]["UploadDate"].ToString() != "")
                {
                    model.UploadDate = DateTime.Parse(ds.Tables[0].Rows[0]["UploadDate"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Comment"] != null && ds.Tables[0].Rows[0]["Comment"].ToString() != "")
                {
                    model.Comment = ds.Tables[0].Rows[0]["Comment"].ToString();
                }
                if (ds.Tables[0].Rows[0]["IsActive"] != null && ds.Tables[0].Rows[0]["IsActive"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["IsActive"].ToString() == "1") || (ds.Tables[0].Rows[0]["IsActive"].ToString().ToLower() == "true"))
                    {
                        model.IsActive = true;
                    }
                    else
                    {
                        model.IsActive = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["ImageCount"] != null && ds.Tables[0].Rows[0]["ImageCount"].ToString() != "")
                {
                    model.ImageCount = int.Parse(ds.Tables[0].Rows[0]["ImageCount"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Type"] != null && ds.Tables[0].Rows[0]["Type"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["Type"].ToString() == "1") || (ds.Tables[0].Rows[0]["Type"].ToString().ToLower() == "true"))
                    {
                        model.Type = true;
                    }
                    else
                    {
                        model.Type = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["DocumentCategory"] != null && ds.Tables[0].Rows[0]["DocumentCategory"].ToString() != "")
                {
                    model.DocumentCategory = int.Parse(ds.Tables[0].Rows[0]["DocumentCategory"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获取地块文本信息
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public Meeting.Model.TopicPlate GetPlateBy(Guid Id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 Id,TopicId,Sort,Path,Name,Format,UploadDate,Comment,IsActive,MeetingId from TopicPlate ");
            strSql.Append(" where Id=@Id ");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.UniqueIdentifier,16)};
            parameters[0].Value = Id;

            Meeting.Model.TopicPlate model = new Meeting.Model.TopicPlate();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["Id"] != null && ds.Tables[0].Rows[0]["Id"].ToString() != "")
                {
                    model.Id = new Guid(ds.Tables[0].Rows[0]["Id"].ToString());
                }
                if (ds.Tables[0].Rows[0]["TopicId"] != null && ds.Tables[0].Rows[0]["TopicId"].ToString() != "")
                {
                    model.TopicId = new Guid(ds.Tables[0].Rows[0]["TopicId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Sort"] != null && ds.Tables[0].Rows[0]["Sort"].ToString() != "")
                {
                    model.Sort = int.Parse(ds.Tables[0].Rows[0]["Sort"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Path"] != null && ds.Tables[0].Rows[0]["Path"].ToString() != "")
                {
                    model.Path = ds.Tables[0].Rows[0]["Path"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Name"] != null && ds.Tables[0].Rows[0]["Name"].ToString() != "")
                {
                    model.Name = ds.Tables[0].Rows[0]["Name"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Format"] != null && ds.Tables[0].Rows[0]["Format"].ToString() != "")
                {
                    model.Format = ds.Tables[0].Rows[0]["Format"].ToString();
                }
                if (ds.Tables[0].Rows[0]["UploadDate"] != null && ds.Tables[0].Rows[0]["UploadDate"].ToString() != "")
                {
                    model.UploadDate = DateTime.Parse(ds.Tables[0].Rows[0]["UploadDate"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Comment"] != null && ds.Tables[0].Rows[0]["Comment"].ToString() != "")
                {
                    model.Comment = ds.Tables[0].Rows[0]["Comment"].ToString();
                }
                if (ds.Tables[0].Rows[0]["IsActive"] != null && ds.Tables[0].Rows[0]["IsActive"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["IsActive"].ToString() == "1") || (ds.Tables[0].Rows[0]["IsActive"].ToString().ToLower() == "true"))
                    {
                        model.IsActive = true;
                    }
                    else
                    {
                        model.IsActive = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["MeetingId"] != null && ds.Tables[0].Rows[0]["MeetingId"].ToString() != "")
                {
                    model.MeetingId = int.Parse(ds.Tables[0].Rows[0]["MeetingId"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 删除一条议题
        /// </summary>
        public JsonResponse DeleteTopic(Guid topicId)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                List<string> sqlList = new List<string>();
                sqlList.Add(string.Format("delete from Topic where Id = '{0}'", topicId));
                sqlList.Add(string.Format("delete from TopicDocument where TopicId = '{0}'", topicId));
                sqlList.Add(string.Format("delete from TopicPlate where TopicId = '{0}'", topicId));
                sqlList.Add(string.Format("delete from TopicApprove where TopicId = '{0}'", topicId));

                int rows = DbHelperSQL.ExecuteSqlTran(sqlList);
                if (rows > 0)
                {
                    response.success = true;
                    response.Message = "";
                }
                else
                {
                    response.success = false;
                    response.Message = "没有可删除的数据！";
                }

                return response;
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return response;
            }
        }

        /// <summary>
        /// 获取议题文档路径列表
        /// </summary>
        /// <param name="topicId"></param>
        /// <returns></returns>
        public List<string> GetTopicDocPaths(Guid topicId)
        {
            try
            {
                string sql = "SELECT distinct Path FROM TopicDocument where TopicId='{0}'";
                DataSet ds = DbHelperSQL.Query(string.Format(sql, topicId));
                //
                if (ds == null)
                {
                    errorString = DbHelperSQL.errorString;
                    return null;
                }
                else
                {
                    List<string> list = new List<string>();
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        list.Add(ds.Tables[0].Rows[i][0].ToString());
                    }

                    return list;
                }
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                return null;
            }
        }

        /// <summary>
        /// 获取议题地块路径列表
        /// </summary>
        /// <param name="topicId"></param>
        /// <returns></returns>
        public List<string> GetTopicPlatePaths(Guid topicId)
        {
            try
            {
                string sql = "SELECT distinct Path FROM TopicPlate where TopicId='{0}'";
                DataSet ds = DbHelperSQL.Query(string.Format(sql, topicId));
                //
                if (ds == null)
                {
                    errorString = DbHelperSQL.errorString;
                    return null;
                }
                else
                {
                    List<string> list = new List<string>();
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        list.Add(ds.Tables[0].Rows[i][0].ToString());
                    }

                    return list;
                }
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                return null;
            }
        }

        /// <summary>
        /// 获取议题审批意见图片路径列表
        /// </summary>
        /// <param name="topicId"></param>
        /// <returns></returns>
        public List<string> GetApprovePicPaths(Guid topicId)
        {
            try
            {
                string sql = "SELECT distinct Path FROM TopicApprove where TopicId='{0}' and Inputtype = 2";
                DataSet ds = DbHelperSQL.Query(string.Format(sql, topicId));
                //
                if (ds == null)
                {
                    errorString = DbHelperSQL.errorString;
                    return null;
                }
                else
                {
                    List<string> list = new List<string>();
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        list.Add(ds.Tables[0].Rows[i][0].ToString());
                    }

                    return list;
                }
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                return null;
            }
        }


        /// <summary>
        /// 删除一条议题文档数据
        /// </summary>
        public string DeleteDocument(Guid id)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("delete from TopicDocument ");
                strSql.Append(" where Id=@Id ");
                SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.UniqueIdentifier,16)};
                parameters[0].Value = id;

                int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
                if (rows > 0)
                {
                    response.success = true;
                    response.Message = "";
                }
                else
                {
                    response.success = false;
                    response.Message = "没有可删除的数据！";
                }

                return JsonConvert.SerializeObject(response);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response);
            }
        }

        /// <summary>
        /// 删除一条议题地块数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string DeletePlate(Guid id)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("delete from TopicPlate ");
                strSql.Append(" where Id=@Id ");
                SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.UniqueIdentifier,16)};
                parameters[0].Value = id;

                int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
                if (rows > 0)
                {
                    response.success = true;
                    response.Message = "";
                }
                else
                {
                    response.success = false;
                    response.Message = "没有可删除的数据！";
                }

                return JsonConvert.SerializeObject(response);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response);
            }
        }

        /// <summary>
        /// 获取相应用户的需要签署意见的议题
        /// </summary>
        /// <param name="employeeId"></param>
        /// <returns></returns>
        public string GetUnApproveBy(string employeeId)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                string sql = "SELECT distinct t.[Id],t.[Title],t.[EmployeeId],t.[EmployeeName],t.[RoleId],t.[CreateTime],t.[IsSubmit] FROM [Topic] t inner join TopicApprove ta on t.Id = ta.TopicId where ta.EmployeeId = '{0}' and ta.IsApprove = 0 order by t.CreateTime desc";
                DataSet ds = DbHelperSQL.Query(string.Format(sql, employeeId));
                //
                if (ds == null)
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }
                else
                {
                    response.success = true;
                    response.ResponseData = ds.Tables[0];
                }

                IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
                timeConverter.DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss";

                return JsonConvert.SerializeObject(response, timeConverter);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response); ;
            }
        }

        /// <summary>
        /// 获取用户的已经签署意见的议题
        /// </summary>
        /// <param name="employeeId"></param>
        /// <returns></returns>
        public string GetApproveBy(string employeeId)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                string sql = "SELECT distinct t.[Id],t.[Title],t.[EmployeeId],t.[EmployeeName],t.[RoleId],t.[CreateTime],t.[IsSubmit] FROM [Topic] t inner join TopicApprove ta on t.Id = ta.TopicId where ta.EmployeeId = '{0}' and ta.IsApprove = 1 order by t.CreateTime desc";
                DataSet ds = DbHelperSQL.Query(string.Format(sql, employeeId));
                //
                if (ds == null)
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }
                else
                {
                    response.success = true;
                    response.ResponseData = ds.Tables[0];
                }

                IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
                timeConverter.DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss";

                return JsonConvert.SerializeObject(response, timeConverter);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response); ;
            }
        }

        /// <summary>
        /// 获取议题的审批意见
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string GetTopicApproveBy(Guid topicId)
        {
            JsonResponse response = new JsonResponse();
            //获取议题
            string tsql = @"SELECT Agent,ExtNumber FROM [dbo].[Topic] where Id = '{0}'";
            string sql = @"SELECT *,'{1}' as Agent,'{2}' as ExtNumber FROM [dbo].[TopicApprove] where TopicId = '{0}'";
            try
            {
                //首先获取议题的经办人
                DataSet ds = DbHelperSQL.Query(string.Format(tsql, topicId));
                if (ds == null && ds.Tables[0].Rows.Count == 0)
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }
                else
                {
                    string agent = ds.Tables[0].Rows[0]["Agent"].ToString();
                    string extNumber = ds.Tables[0].Rows[0]["ExtNumber"].ToString();
                    ds = DbHelperSQL.Query(string.Format(sql, topicId, agent, extNumber));
                    //
                    if (ds == null)
                    {
                        response.success = false;
                        response.Message = DbHelperSQL.errorString;
                    }
                    else
                    {
                        response.success = true;
                        response.ResponseData = ds.Tables[0];
                    }
                }
                IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
                timeConverter.DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss";


                return JsonConvert.SerializeObject(response, timeConverter);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response); ;
            }
        }

        /// <summary>
        /// 设置议题提交上会
        /// </summary>
        /// <param name="topicId"></param>
        /// <returns></returns>
        public string SubmitMeeting(Guid topicId)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                //提交上会之前首先要判断一下是否提交了议题呈批表，如果没有返回false，提示上传
                //首先获取议题的经办人
                //DataSet ds = DbHelperSQL.Query(string.Format("SELECT [Id] FROM TopicDocument where [TopicId] = '{0}' and DocumentCategory = 1 ", topicId));
                //if (ds == null)
                //{
                //    response.success = false;
                //    response.Message = DbHelperSQL.errorString;
                //}
                //else if (ds.Tables[0].Rows.Count == 0)
                //{
                //    response.success = false;
                //    response.Message = "提交上会之前，请首先提交呈批表！";
                //}
                if (false)
                { }
                else
                {
                    //验证成功之后，提交上会
                    int obj = DbHelperSQL.ExecuteSql(string.Format("UPDATE [Topic] SET [IsSubmit] = 1 WHERE  [Id] = '{0}'", topicId));
                    if (obj > 0)
                    {
                        response.success = true;
                        response.Message = "";
                    }
                    else
                    {
                        response.success = false;
                        response.Message = DbHelperSQL.errorString;
                    }
                }

                return JsonConvert.SerializeObject(response);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response);
            }
        }

        /// <summary>
        /// 对遗议题签署意见
        /// </summary>
        /// <param name="topicId"></param>
        /// <returns></returns>
        public string SubmitApprove(Model.TopicApprove model)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                int obj = DbHelperSQL.ExecuteSql(string.Format("UPDATE [TopicApprove] SET [Comment] = '{2}',[IsApprove] = 1,ApproveDate=getdate(),[InputType] = {3},Path='{4}' WHERE  [TopicId] = '{0}' and [EmployeeId] = '{1}'", model.TopicId, model.EmployeeId, model.Comment, model.InputType, model.Path));
                if (obj > 0)
                {
                    response.success = true;
                    response.Message = "";
                }
                else
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }

                return JsonConvert.SerializeObject(response);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response);
            }
        }
    }
}
